﻿/***
 * Ixsd2db - Infopath XSD to DB table create tool
 * CODEPLEX
 * AUTHER: SHARON JOSEPH
 * LEAD: MAULIK SONI
 **/
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Schema;

namespace WindowsFormsApplication1
{
    public partial class Create_Alter : Form
    {
        string connectionString = " ";
        SqlConnection sqlCon;
        DataTable dt = new DataTable();
        DataColumn col1, col2, col3;

        public Create_Alter()
        {
            InitializeComponent();

            txtpassword.PasswordChar = '*';
            dataGridView1.DataSource = dt;
            col1 = new DataColumn();
            col1.ColumnName = "Table Name";
            dt.Columns.Add(col1);
            col2 = new DataColumn();
            col2.ColumnName = "Operation";
            dt.Columns.Add(col2);
            col3 = new DataColumn();
            col3.ColumnName = "Field Names";
            dt.Columns.Add(col3);
            dataGridView1.Visible = false;

        }

        private void btnCreateAlter_Click(object sender, EventArgs e)
        {
            try
            {


                if (txtFormTableName.Text == "" || txtFilePath.Text == "" || connectionString.Trim() == "")
                {
                    string message = "Please enter required fields";
                    string caption = "Missing Values";
                    MessageBoxButtons buttons = MessageBoxButtons.OK;
                    DialogResult result;
                    result = MessageBox.Show(this, message, caption, buttons);
                }
                else
                {
                    dataGridView1.Visible = true;
                    string fpath1 = "";

                    fpath1 = txtFilePath.Text;
                    string[] elementname = new string[10000];//for storing column names
                    string[] elementnull = new string[10000];//for storing null or not null values 
                    string[] elementtype = new string[10000];//for storing data types

                    int i = 0;
                    int name = 0, type = 0, nul = 0;
                    try
                    {
                        XmlTextReader reader = new XmlTextReader(fpath1);

                        while (reader.Read())
                        {
                            switch (reader.NodeType)
                            {
                                case XmlNodeType.Element: // The node is an element.
                                    if (reader.Name == "xsd:element")
                                    {

                                        while (reader.MoveToNextAttribute()) // Read the attributes.
                                        {
                                            if (reader.AttributeCount > 1)//the very first xsd:element has the form name and no other attributes
                                            {
                                                if (reader.Name == "ref" || reader.Name == "minoccurs")
                                                {
                                                    name = 0;
                                                }
                                                if (reader.Name == "name")
                                                {
                                                    elementname[i] = reader.Value;
                                                    name = 1;
                                                }
                                                if (reader.Name == "type")
                                                {
                                                    string xsd = "xsd:"; //the type is always stored with a xsd: in the xsd files, so strip the xsd: before storing the type in the columntype array
                                                    int firstcharacter12 = reader.Value.IndexOf(xsd);
                                                    firstcharacter12 = firstcharacter12 + xsd.Length;
                                                    string fieldtype12 = reader.Value.Substring(firstcharacter12);
                                                    elementtype[i] = fieldtype12;
                                                    type = 1;
                                                }
                                                if (reader.Name == "nillable")
                                                {
                                                    elementnull[i] = reader.Value;
                                                    nul = 1;
                                                }
                                            }
                                            else
                                            {
                                                name = 0;
                                            }

                                        }

                                        if (name == 1 && type == 0)
                                        {
                                            elementtype[i] = "Varchar(500)";
                                        }

                                        if (name == 1 && nul == 0)
                                        {
                                            elementnull[i] = "false";
                                        }
                                        if (name != 0)
                                        {
                                            i++;
                                        }
                                        name = 0;
                                        nul = 0;
                                        type = 0;

                                    }
                                    break;
                            }
                        }

                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }

                    string table = "";
                    string commandString = "";
                    string[] tablename = txtFormTableName.Text.Split(' '); //get the form name from user to construct the table name and split them into individual words to remove spaces
                    if (tablename.Length > 1)
                    {
                        for (int a = 0; a < tablename.Length; a++)
                        {
                            for (int b = 0; b < tablename[a].Length; b++) //check each character in each word for symbols that are not accepted by SQL
                            {
                                string c = tablename[a];
                                if (c[b] != '-')
                                {
                                    table += c[b];

                                }
                            }

                            if (tablename.Length - 1 != a)
                            {
                                table += "_";
                            }

                        }
                    }
                    else
                    {
                        table = txtFormTableName.Text; //if the form name is already just a single word, use that as the table name
                    }
                    //once the xsd is read, check if the table needs to be created or altered
                    string[] tablenames = new string[10000];
                    int t = 0;
                    SqlDataReader myReader = null;
                    SqlCommand myCommand = new SqlCommand("Select TABLE_NAME from Information_Schema.Tables", sqlCon); //get all the table names from the information schema table
                    myReader = myCommand.ExecuteReader();

                    while (myReader.Read())
                    {
                        tablenames[t] = myReader["TABLE_NAME"].ToString();
                        t++;

                    }
                    myReader.Close();
                    int alter = 0;
                    for (int v = 0; v < t; v++)
                    {
                        if (tablenames[v].Trim() == table.Trim()) //if table already exists then alter the table
                        {
                            //create = 0;
                            alter = 1;
                            break;
                        }

                    }

                    if (alter == 1)
                    {
                        //code to alter the table
                        SqlDataReader myReader1 = null;
                        string myCom;
                        myCom = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME ='"; //get all the column names for the selected table
                        myCom += table;
                        myCom += "'";
                        SqlCommand myCommand1 = new SqlCommand(myCom, sqlCon);
                        myReader1 = myCommand1.ExecuteReader();
                        string[] colnames = new string[10000];
                        int t1 = 0;
                        while (myReader1.Read()) //reading already existing column names within the database
                        {
                            t1++;
                            colnames[t1] = myReader1["COLUMN_NAME"].ToString();
                        }
                        myReader1.Close();
                        int yes = 0;
                        int ge = 0;
                        int e1 = i;
                        //check against the column names read from the xsd file
                        if (t1 != e1) //if there is a change in the xsd file as compared to the database - to avoid errorneous submissions by the user
                        {

                            if (t1 < e1) //to add a field - if the elements in xsd file is more than the columns in the database
                            {
                                txtusername.Text = "";
                                for (int pe = 0; pe < e1; pe++)//for every column name read from the xsd file 
                                {
                                    for (ge = 1; ge <= t1; ge++) //for every column name fetched from the database
                                    {

                                        if (colnames[ge] == elementname[pe]) //if there is match set yes to 1
                                        {

                                            yes = 1;

                                            break;
                                        }


                                    }

                                    if (yes == 0) //if yes was never set to 1 for this particular element, then add the column
                                    {


                                        //found a new column name
                                        commandString = "Alter Table ";
                                        commandString += table;
                                        commandString += " ";
                                        commandString += "ADD";
                                        commandString += " ";
                                        commandString += elementname[pe];
                                        commandString += " ";
                                        //mapping data types from XSD file to suitable SQL data types
                                        if (elementtype[pe] == "integer")
                                        {
                                            commandString += "INT";


                                        }
                                        if (elementtype[pe] == "dateTime")
                                        {
                                            commandString += "date";


                                        }
                                        if (elementtype[pe] == "string" || elementtype[pe] == "requiredString")
                                        {
                                            commandString += "VARCHAR(MAX)";

                                        }
                                        if (elementtype[pe] == "boolean")
                                        {
                                            commandString += "BIT";

                                        }
                                        if (elementtype[pe] == "double")
                                        {
                                            commandString += "float";

                                        }
                                        if (elementtype[pe] == "time")
                                        {
                                            commandString += "nvarchar(50)";

                                        }


                                        if (elementtype[pe] != "boolean" && elementtype[pe] != "time" && elementtype[pe] != "string" && elementtype[pe] != "integer" && elementtype[pe] != "requiredString" && elementtype[pe] != "double" && elementtype[pe] != "dateTime")
                                        {
                                            commandString += elementtype[pe];

                                        }
                                        commandString += " ";

                                        if (elementnull[pe] == "true")
                                        {
                                            commandString += " ";
                                            commandString += "NOT NULL";
                                        }
                                        else
                                        {
                                            commandString += "NULL";
                                        }

                                        DataRow myNewRow;
                                        myNewRow = dt.NewRow();
                                        myNewRow[col1] = table;
                                        myNewRow[col2] = "Column Addition";
                                        myNewRow[col3] = elementname[pe];
                                        dt.Rows.Add(myNewRow);

                                        SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                                        sqlCmd = new SqlCommand(commandString, sqlCon);
                                        sqlCmd.ExecuteNonQuery();

                                    }
                                    else
                                    {
                                        yes = 0;
                                    }




                                }
                                string message = "Table Altered successfully - Columns were added";
                                string caption = "Alter Success";
                                MessageBoxButtons buttons = MessageBoxButtons.OK;
                                DialogResult result;
                                result = MessageBox.Show(this, message, caption, buttons);
                            }
                            if (e1 < t1)//to delete a field - if the number of columns in database is more than the number of elements in the xsd file
                            {

                                int pe;
                                for (pe = 1; pe <= t1; pe++) //for every column name read from the database
                                {
                                    for (ge = 0; ge < e1; ge++) //for every element name fetched from the xsd file
                                    {


                                        if (colnames[pe] == elementname[ge]) //check if the column name exists in the xsd file
                                        {

                                            yes = 1;
                                            //  break;
                                        }


                                    }
                                    if (yes == 0) //if column name does not exist in the xsd file, delete the column
                                    {
                                        commandString = "Alter Table ";
                                        commandString += table;
                                        commandString += " ";
                                        commandString += "DROP Column";
                                        commandString += " ";
                                        commandString += colnames[pe];

                                        try
                                        {
                                            DataRow myNewRow;
                                            myNewRow = dt.NewRow();
                                            myNewRow[col1] = table;
                                            myNewRow[col2] = "Column Deletion";
                                            myNewRow[col3] = colnames[pe];
                                            dt.Rows.Add(myNewRow);
                                            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                                            sqlCmd = new SqlCommand(commandString, sqlCon);
                                            sqlCmd.ExecuteNonQuery();
                                        }
                                        catch (Exception ex)
                                        {
                                            MessageBox.Show(ex.Message);
                                        }


                                    }
                                    else
                                    {
                                        yes = 0;
                                    }

                                }

                                string message = "Table Altered successfully - Columns were deleted";
                                string caption = "Alter Success";
                                MessageBoxButtons buttons = MessageBoxButtons.OK;
                                DialogResult result;
                                result = MessageBox.Show(this, message, caption, buttons);
                            }
                        }
                        else
                        {
                            string message = "There were no changes detected in the xsd file - Did you forget generating a new XSD source file after making changes to the Infopath form?";
                            string caption = "Erroneous Submission";
                            MessageBoxButtons buttons = MessageBoxButtons.YesNo;
                            DialogResult result;
                            result = MessageBox.Show(this, message, caption, buttons);
                            if (result == DialogResult.Yes)
                            {
                                //this.Close();
                                string message1 = "Please update the XSD file and try again.";
                                string caption1 = "Erroneous Submission";
                                MessageBoxButtons buttons1 = MessageBoxButtons.OK;
                                DialogResult result1;
                                result1 = MessageBox.Show(this, message1, caption1, buttons1);

                            }
                            if (result == DialogResult.No)
                            {
                                string message1 = "There were still no changes detected in the xsd file. Please check the path where the XSD file resides and try again.";
                                string caption1 = "Erroneous Submission";
                                MessageBoxButtons buttons1 = MessageBoxButtons.OK;
                                DialogResult result1;
                                result1 = MessageBox.Show(this, message1, caption1, buttons1);
                            }

                        }

                    }

                    else//if table does not exist, create the  table
                    {

                        commandString += "CREATE TABLE ";
                        commandString += table;
                        commandString += "(";
                        //type conversions
                        int e1 = i;
                        e1--;
                        for (int g = 0; g <= e1; g++)
                        {

                            commandString += elementname[g];
                            commandString += " ";
                            if (elementtype[g] == "integer")
                            {
                                commandString += "INT";
                            }
                            if (elementtype[g] == "dateTime")
                            {
                                commandString += "date";


                            }
                            if (elementtype[g] == "string" || elementtype[g] == "requiredString")
                            {
                                commandString += "VARCHAR(500)";

                            }
                            if (elementtype[g] == "boolean")
                            {
                                commandString += "BIT";

                            }
                            if (elementtype[g] == "double")
                            {
                                commandString += "float";

                            }
                            if (elementtype[g] == "time")
                            {
                                commandString += "nvarchar(50)";

                            }


                            if (elementtype[g] != "boolean" && elementtype[g] != "time" && elementtype[g] != "string" && elementtype[g] != "integer" && elementtype[g] != "requiredString" && elementtype[g] != "double" && elementtype[g] != "dateTime")
                            {
                                commandString += elementtype[g];

                            }

                            if (elementnull[g] == "false")
                            {
                                commandString += " ";
                                commandString += "NOT NULL";
                            }


                            if (e1 != g)
                            {

                                commandString += ",";
                            }
                        }
                        commandString += ")";

                        //execute the query
                        DataRow myNewRow;
                        myNewRow = dt.NewRow();
                        myNewRow[col1] = table;
                        myNewRow[col2] = "Table Creation";
                        myNewRow[col3] = " ";
                        dt.Rows.Add(myNewRow);
                        SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                        sqlCmd = new SqlCommand(commandString, sqlCon);

                        sqlCmd.ExecuteNonQuery();
                        //sqlCon.Close();
                        string message = "Table created successfully";
                        string caption = "Success";
                        MessageBoxButtons buttons = MessageBoxButtons.OK;
                        DialogResult result;
                        result = MessageBox.Show(this, message, caption, buttons);
                        // this.Close();


                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            try
            {
                openFileDialog1.InitialDirectory = @"C:\";
                openFileDialog1.Title = "Select a File";
                openFileDialog1.Filter = "XSD files (*.xsd)|*.xsd";
                openFileDialog1.FileName = "";
                openFileDialog1.ShowDialog();
                if (openFileDialog1.FileName != "")
                {
                    txtFilePath.Text = openFileDialog1.FileName;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnTestConnection_Click(object sender, EventArgs e)
        {
            try
            {
                connectionString = txtconnectionString.Text;
                if (connectionString.Trim() == "")
                {
                    connectionString = "Data Source=" + txtdatasource.Text.Trim() + ";Initial Catalog=" + txtdbname.Text.Trim() + ";User ID=" + txtusername.Text.Trim() + ";Password=" + txtpassword.Text.Trim();
                    if (txtdatasource.Text.Trim() == "" && txtusername.Text.Trim() == "" && txtpassword.Text.Trim() == "" && txtdbname.Text.Trim() == "")
                    {

                        string message = "You did not enter a Database Connection String.";
                        string caption = "No Connection string specified";
                        MessageBoxButtons buttons = MessageBoxButtons.OK;
                        DialogResult result;
                        result = MessageBox.Show(this, message, caption, buttons);
                        connectionString = "";
                    }
                    else
                    {
                        try
                        {
                            sqlCon = new SqlConnection(connectionString);
                            sqlCon.Open();
                            string message = "Connection Success";
                            string caption = "Successful Connection";
                            MessageBoxButtons buttons = MessageBoxButtons.OK;
                            DialogResult result;
                            result = MessageBox.Show(this, message, caption, buttons);

                        }
                        catch (Exception e2)
                        {
                            string message = e2.Message;
                            string caption = "Connection Failed";
                            MessageBoxButtons buttons = MessageBoxButtons.OK;
                            DialogResult result;
                            result = MessageBox.Show(this, message, caption, buttons);
                        }
                    }
                }
                else
                {
                    try
                    {
                        sqlCon = new SqlConnection(connectionString);
                        sqlCon.Open();
                        string message = "Connection Success";
                        string caption = "Successful Connection";
                        MessageBoxButtons buttons = MessageBoxButtons.OK;
                        DialogResult result;
                        result = MessageBox.Show(this, message, caption, buttons);

                    }
                    catch (Exception e2)
                    {
                        string message = e2.Message;
                        string caption = "Connection Failed";
                        MessageBoxButtons buttons = MessageBoxButtons.OK;
                        DialogResult result;
                        result = MessageBox.Show(this, message, caption, buttons);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

    }

}

